WITH 
-- 步骤1: 过滤给定a值，并计算每个(b,c,d)组合的出现次数
cte1 AS (
        SELECT 
                *,
                        COUNT(*) OVER (PARTITION BY b, c, d) AS bcd_count  -- 计算每个(b,c,d)组的行数
                            FROM your_table
                                WHERE a = 'a_value'  -- 替换 'a_value' 为实际的a值，注意类型（如数字则去掉引号）
),
-- 步骤2: 找出bcd_count的最大值
max_bcd AS (
        SELECT MAX(bcd_count) AS max_bcd
            FROM cte1
),
-- 步骤3: 获取所有bcd_count等于最大值的行（即b,c,d相同数量最多的行）
cte2 AS (
        SELECT *
            FROM cte1
                WHERE bcd_count = (SELECT max_bcd FROM max_bcd)
),
-- 步骤4: 在cte2的基础上，计算每个(c,d)组合的出现次数
cte3 AS (
        SELECT 
                *,
                        COUNT(*) OVER (PARTITION BY c, d) AS cd_count  -- 计算每个(c,d)组的行数
                            FROM cte2
),
-- 步骤5: 找出cd_count的最大值
max_cd AS (
        SELECT MAX(cd_count) AS max_cd
            FROM cte3
),
-- 步骤6: 获取所有cd_count等于最大值的行（即c,d相同数量最多的行）
cte4 AS (
        SELECT *
            FROM cte3
                WHERE cd_count = (SELECT max_cd FROM max_cd)
),
-- 步骤7: 在cte4的基础上，计算每个c值的出现次数
cte5 AS (
        SELECT 
                *,
                        COUNT(*) OVER (PARTITION BY c) AS c_count  -- 计算每个c组的行数
                            FROM cte4
),
-- 步骤8: 找出c_count的最大
        SELECT *
            FROM cte5
                WHERE c_count = (S
















WITH 
-- 步骤1: 过滤给定a值，并计算每个(b,c,d)组合的出现次数
cte1 AS (
        SELECT 
                *,
                        COUNT(*) OVER (PARTITION BY b, c, d) AS bcd_count  -- 计算每个(b,c,d)组的行数
                            FROM your_table
                                WHERE a = 'a_value'  -- 替换 'a_value' 为实际的a值，注意类型（如数字则去掉引号）
),
-- 步骤2: 找出bcd_count的最大值
max_bcd AS (
        SELECT MAX(bcd_count) AS max_bcd
            FROM cte1
),
-- 步骤3: 获取所有bcd_count等于最大值的行（即b,c,d相同数量最多的行）
cte2 AS (
        SELECT *
            FROM cte1
                WHERE bcd_count = (SELECT max_bcd FROM max_bcd)
),
-- 步骤4: 在cte2的基础上，计算每个(c,d)组合的出现次数
cte3 AS (
        SELECT 
                *,
                        COUNT(*) OVER (PARTITION BY c, d) AS cd_count  -- 计算每个(c,d)组的行数
                            FROM cte2
),
-- 步骤5: 找出cd_count的最大值
max_cd AS (
        SELECT MAX(cd_count) AS max_cd
            FROM cte3
),
-- 步骤6: 获取所有cd_count等于最大值的行（即c,d相同数量最多的行）
cte4 AS (
        SELECT *
            FROM cte3
                WHERE cd_count = (SELECT max_cd FROM max_cd)
),
-- 步骤7: 在cte4的基础上，计算每个c值的出现次数
cte5 AS (
        SELECT 
                *,
                        COUNT(*) OVER (PARTITION BY c) AS c_count  -- 计算每个c组的行数
                            FROM cte4
),
-- 步骤8: 找出c_count的最大值
max_c AS (
        SELECT MAX(c_count) AS max_c
            FROM cte5
),
-- 步骤9: 获取所有c_count等于最大值的行（即c相同数量最多的行）
final AS (
        SELECT *
            FROM cte5
                WHERE c_count = (SELECT max_c FROM max_c)
)
-- 最终返回所需的列（原始数据，不包括聚合列）
SELECT 
    a, b, c, d  -- 可以添加其他列，如SELECT *，但聚合列（bcd_count等）通常不需要
    FROM final;
)
)
)
)
)
)
)
)
)











-- 方案 1：标准方法（推荐）
USE YourDatabase;
GO
SELECT 
    COLUMN_NAME,
        DATA_TYPE,
            CHARACTER_MAXIMUM_LENGTH,
                IS_NULLABLE,
                    COLUMN_DEFAULT
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = 'YourTable';

                    -- 方案 2：替代视图（当 INFORMATION_SCHEMA 不可用时）
                    SELECT
                        c.name AS ColumnName,
                            t.name AS DataType,
                                CASE 
                                        WHEN t.name IN ('nvarchar', 'nchar') THEN c.max_length/2
                                                WHEN t.name IN ('varchar', 'char', 'binary') THEN c.max_length
                                                        ELSE NULL 
                                                            END AS MaxLength,
                                                                CASE c.is_nullable WHEN 1 THEN 'YES' ELSE 'NO' END AS Nullable,
                                                                    d.definition AS DefaultValue
                                                                    FROM sys.columns c
                                                                    JOIN sys.types t ON c.user_type_id = t.user_type_id
                                                                    LEFT JOIN sys.default_constraints d ON c.default_object_id = d.object_id
                                                                    WHERE c.object_id = OBJECT_ID
                                                                    ('dbo.YourTable');











-- 替代方案 1：sys.columns + sys.tables
SELECT 
    c.name AS ColumnName,
        t.name AS DataType,
            c.max_length AS MaxLength,
                c.is_nullable AS IsNullable
                FROM sys.columns c
                JOIN sys.types t ON c.user_type_id = t.user_type_id
                WHERE c.object_id = OBJECT_ID('dbo.YourTable');

                -- 替代方案 2：sys.objects + sys.columns
                SELECT 
                    col.name AS ColumnName,
                        TYPE_NAME(col.user_type_id) AS DataType,
                            col.max_length,
                                col.precision,
                                    col.scale
                                    FROM sys.objects obj
                                    JOIN sys.columns col ON obj.object_id = col.object_id
                                    WHERE obj.name = 'YourTable'
                                      AND obj.type = 'U';  -- U = 用户表

